<!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>MySql&#x6743;&#x9650;&#x7ba1;&#x7406;</title>
        <style>
/* From extension vscode.github */
/*---------------------------------------------------------------------------------------------
 *  Copyright (c) Microsoft Corporation. All rights reserved.
 *  Licensed under the MIT License. See License.txt in the project root for license information.
 *--------------------------------------------------------------------------------------------*/

.vscode-dark img[src$=\#gh-light-mode-only],
.vscode-light img[src$=\#gh-dark-mode-only] {
	display: none;
}

</style>
        
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/Microsoft/vscode/extensions/markdown-language-features/media/markdown.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/Microsoft/vscode/extensions/markdown-language-features/media/highlight.css">
<style>
            body {
                font-family: -apple-system, BlinkMacSystemFont, 'Segoe WPC', 'Segoe UI', system-ui, 'Ubuntu', 'Droid Sans', sans-serif;
                font-size: 14px;
                line-height: 1.6;
            }
        </style>
        <style>
.task-list-item {
    list-style-type: none;
}

.task-list-item-checkbox {
    margin-left: -20px;
    vertical-align: middle;
    pointer-events: none;
}
</style>
        
    </head>
    <body class="vscode-body vscode-light">
        <ul>
<li><a href="#mysql%E6%9D%83%E9%99%90%E7%AE%A1%E7%90%86">MySql权限管理</a></li>
<li><a href="#dcl%E6%95%B0%E6%8D%AE%E5%BA%93%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80">DCL（数据库控制语言）</a></li>
<li><a href="#mysql%E6%9D%83%E9%99%90%E8%A1%A8">Mysql权限表</a>
<ul>
<li><a href="#mysqluser">mysql.user</a></li>
<li><a href="#mysqldb">mysql.db</a></li>
<li><a href="#mysqltables_privmysqlcolumns_privprocs_priv">mysql.tables_priv，mysql.columns_priv、procs_priv</a></li>
<li><a href="#%E6%8E%88%E6%9D%83%E7%BA%A7%E5%88%AB%E6%8E%92%E5%88%97">授权级别排列</a></li>
<li><a href="#%E8%A7%A3%E5%86%B3%E8%BF%9C%E7%A8%8B%E8%BF%9E%E6%8E%A5">解决远程连接</a></li>
<li><a href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E5%92%8C%E8%A1%A8%E6%A0%BC%E5%BC%8F">数据库和表格式</a></li>
<li><a href="#%E7%94%A8%E6%88%B7%E5%92%8C-ip%E6%A0%BC%E5%BC%8F">用户和 IP格式</a></li>
</ul>
</li>
<li><a href="#mysql-%E7%94%A8%E6%88%B7%E7%AE%A1%E7%90%86">MySQL 用户管理</a>
<ul>
<li><a href="#%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7">创建用户</a></li>
<li><a href="#%E5%88%A0%E9%99%A4%E7%94%A8%E6%88%B7">删除用户</a></li>
<li><a href="#%E4%BF%AE%E6%94%B9%E7%94%A8%E6%88%B7">修改用户</a></li>
<li><a href="#%E4%BF%AE%E6%94%B9%E5%AF%86%E7%A0%81">修改密码</a></li>
<li><a href="#%E6%89%BE%E5%9B%9E-root-%E5%AF%86%E7%A0%81">找回 root 密码</a></li>
<li><a href="#%E6%B3%A8%E9%87%8A%E6%8E%89%E5%85%8D%E5%AF%86%E7%99%BB%E5%BD%95">注释掉免密登录</a></li>
<li><a href="#%E9%87%8D%E5%90%AF-mysql-%E7%84%B6%E5%90%8E%E7%99%BB%E5%BD%95">重启 MySQL 然后登录</a></li>
<li><a href="#%E5%AF%86%E7%A0%81%E5%A4%8D%E6%9D%82%E5%BA%A6">密码复杂度</a></li>
<li><a href="#%E5%AF%86%E7%A0%81%E4%B8%8D%E7%AC%A6%E5%90%88%E5%A4%8D%E6%9D%82%E6%80%A7">密码不符合复杂性</a></li>
</ul>
</li>
<li><a href="#mysql-%E7%99%BB%E5%BD%95">MySQL 登录</a></li>
<li><a href="#mysql-%E6%9D%83%E9%99%90%E7%AE%A1%E7%90%86">MySQL 权限管理</a>
<ul>
<li><a href="#%E6%9F%A5%E7%9C%8B%E6%9D%83%E9%99%90">查看权限</a></li>
<li><a href="#%E6%8E%88%E6%9D%83%E5%8F%8A%E8%AE%BE%E7%BD%AE%E5%AF%86%E7%A0%81">授权及设置密码</a></li>
<li><a href="#mysql-8x-%E6%8E%88%E6%9D%83%E6%96%B9%E5%BC%8F">MySQL 8.x 授权方式</a></li>
<li><a href="#%E5%88%B7%E6%96%B0%E6%9D%83%E9%99%90">刷新权限</a></li>
</ul>
</li>
</ul>
<h1 id="mysql权限管理">MySql权限管理</h1>
<h1 id="dcl数据库控制语言">DCL（数据库控制语言）</h1>
<blockquote>
<p>数据库授权、角色控制等操作</p>
</blockquote>
<blockquote>
<p>GRANT 用户授权，为用户赋予访问权限</p>
</blockquote>
<blockquote>
<p>REVOKE 取消授权，撤回授权权限</p>
</blockquote>
<h1 id="mysql权限表">Mysql权限表</h1>
<h2 id="mysqluser">mysql.user</h2>
<p>用户字段：Host、User、Password</p>
<p>权限字段：_Priv结尾的字段</p>
<p>安全字段：ssl x509字段</p>
<p>资源控制字段：max_开头的字段</p>
<h2 id="mysqldb">mysql.db</h2>
<p>用户字段：Host、User、Password</p>
<p>权限字段：剩下的_Priv结尾的字段</p>
<h2 id="mysqltables_privmysqlcolumns_privprocs_priv">mysql.tables_priv，mysql.columns_priv、procs_priv</h2>
<p>表、列、存储过程的授权表</p>
<h2 id="授权级别排列">授权级别排列</h2>
<p>mysql.user #全局授权</p>
<p>mysql.db #数据库级别授权</p>
<p>其他 #表级，列级授权</p>
<h2 id="解决远程连接">解决远程连接</h2>
<pre><code class="language-sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> PRIVILEGES <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;root&#x27;</span>@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;密码&#x27;</span> <span class="hljs-keyword">WITH</span> <span class="hljs-keyword">GRANT</span> OPTION
</code></pre>
<h2 id="数据库和表格式">数据库和表格式</h2>
<blockquote>
<p>数据库名.* 数据库中的所有</p>
</blockquote>
<blockquote>
<p>数据库名.表名 指定数据库中的某张表</p>
</blockquote>
<blockquote>
<p>数据库名.存储过程 指定数据库中的存储过程</p>
</blockquote>
<blockquote>
<p>*.* 所有数据库</p>
</blockquote>
<h2 id="用户和-ip格式">用户和 IP格式</h2>
<blockquote>
<p>用户名@IP地址 用户只能在改IP下才能访问</p>
</blockquote>
<blockquote>
<p>用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)</p>
</blockquote>
<blockquote>
<p>用户名@%.qfedu.com</p>
</blockquote>
<blockquote>
<p>用户名@% 用户可以再任意IP下访问(默认IP地址为%)</p>
</blockquote>
<h1 id="mysql-用户管理">MySQL 用户管理</h1>
<h2 id="创建用户">创建用户</h2>
<blockquote>
<p>CREATE USER语句创建</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;用户名&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span> [ IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;密码&#x27;</span> ]
</code></pre>
<blockquote>
<p>GRANT语句创建</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;用户名&#x27;</span>@’IP地址’ IDENTIFIED <span class="hljs-keyword">BY</span> &quot;密码&quot;
</code></pre>
<blockquote>
<p>创建实例</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;qfedu&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;123456&#x27;</span>;
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;qfedu&#x27;</span>@<span class="hljs-string">&#x27;192.168.1.101&#x27;</span> IDENDIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;123456&#x27;</span>;
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;qfedu&#x27;</span>@<span class="hljs-string">&#x27;192.168.1.%&#x27;</span> IDENDIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;123456&#x27;</span>;
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;qfedu&#x27;</span>@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;123456&#x27;</span>;
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;user3&#x27;</span>@’localhost’ IDENTIFIED <span class="hljs-keyword">BY</span> ‘<span class="hljs-number">123456</span>’;
</code></pre>
<h2 id="删除用户">删除用户</h2>
<blockquote>
<p>DROP USER 删除</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;用户名&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span>;
</code></pre>
<blockquote>
<p>实例</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;user1&#x27;</span>@’localhost’;
</code></pre>
<blockquote>
<p>DELETE 语句删除</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> mysql.user <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">user</span><span class="hljs-operator">=</span><span class="hljs-string">&#x27;用户名&#x27;</span> <span class="hljs-keyword">AND</span> host<span class="hljs-operator">=</span><span class="hljs-string">&#x27;IP地址&#x27;</span>
</code></pre>
<blockquote>
<p>实例</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> mysql.user <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">user</span><span class="hljs-operator">=</span>’user2’ <span class="hljs-keyword">AND</span> host<span class="hljs-operator">=</span>’localhost’
</code></pre>
<h2 id="修改用户">修改用户</h2>
<pre><code class="language-sql">RENAME <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;旧用户名&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;新用户名&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span> ;
</code></pre>
<blockquote>
<p>实例</p>
</blockquote>
<pre><code class="language-sql">RENAME <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;old_user&#x27;</span>@‘localhost’ <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;new_user&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span>;
</code></pre>
<h2 id="修改密码">修改密码</h2>
<blockquote>
<p><code>注意</code>：修改完密码必须刷新权限</p>
</blockquote>
<pre><code class="language-sql">FLUSH PRIVILEGES;
</code></pre>
<blockquote>
<p>root 用户修改自己密码</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-comment">-- 方法一：</span>
mysqladmin <span class="hljs-operator">-</span>u root <span class="hljs-operator">-</span>p <span class="hljs-number">123</span> password <span class="hljs-string">&#x27;new_password&#x27;</span> 
<span class="hljs-comment">-- 123为旧密码</span>
<span class="hljs-comment">-- 方法二：</span>
<span class="hljs-keyword">alter</span> <span class="hljs-keyword">user</span> <span class="hljs-string">&#x27;root&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">&#x27;new_pssword&#x27;</span>;
<span class="hljs-comment">-- 方法三：</span>
<span class="hljs-comment">/* 较早版本的mysql */</span>
<span class="hljs-keyword">SET</span> PASSWORD <span class="hljs-keyword">FOR</span> <span class="hljs-string">&#x27;root&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span> <span class="hljs-operator">=</span> PASSWORD(<span class="hljs-string">&#x27;new_password&#x27;</span>);
<span class="hljs-keyword">SET</span> PASSWORD<span class="hljs-operator">=</span>password(‘new_password’);
</code></pre>
<blockquote>
<p>root 修改其他用户密码</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-comment">-- 方法一：</span>
<span class="hljs-keyword">alter</span> <span class="hljs-keyword">user</span> <span class="hljs-string">&#x27;qfedu&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">&#x27;Qfedu.1234com&#x27;</span>;
<span class="hljs-comment">-- 方法三：</span>
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> 用户名@’ip地址’ IDENTIFIED <span class="hljs-keyword">BY</span> ‘yuan’;
</code></pre>
<blockquote>
<p>普通用户修改自己密码</p>
</blockquote>
<pre><code class="language-sql"><span class="hljs-keyword">SET</span> password<span class="hljs-operator">=</span>password(‘new_password’);
</code></pre>
<h2 id="找回-root-密码">找回 root 密码</h2>
<p>修改 MySQL 配置文件</p>
<p>在<code>[mysqld]</code>下面加上 <code>skip-grant-tables</code></p>
<pre><code class="language-sql">[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# vim <span class="hljs-operator">/</span>etc<span class="hljs-operator">/</span>my.cnf
[mysqld]
···
#设置免密登录
<span class="hljs-keyword">skip</span><span class="hljs-operator">-</span><span class="hljs-keyword">grant</span><span class="hljs-operator">-</span>tables
</code></pre>
<p>重启 MySQL</p>
<pre><code class="language-sql">[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# systemctl restart mysqld
</code></pre>
<p>修改密码</p>
<p>终端输入 mysql 直接登录 MySQL数据库</p>
<pre><code class="language-sql">[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# mysql
</code></pre>
<p>切换到 MySQL 系统库 mysql</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> use mysql;
</code></pre>
<p>设置密码</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">set</span> authentication_string<span class="hljs-operator">=</span>password(<span class="hljs-string">&#x27;密码&#x27;</span>) <span class="hljs-keyword">where</span> <span class="hljs-keyword">user</span><span class="hljs-operator">=</span><span class="hljs-string">&#x27;root&#x27;</span>;
</code></pre>
<h2 id="注释掉免密登录">注释掉免密登录</h2>
<pre><code class="language-sql">[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# vim <span class="hljs-operator">/</span>etc<span class="hljs-operator">/</span>my.cnf
[mysqld]
···
#设置免密登录
#<span class="hljs-keyword">skip</span><span class="hljs-operator">-</span><span class="hljs-keyword">grant</span><span class="hljs-operator">-</span>tables
</code></pre>
<h2 id="重启-mysql-然后登录">重启 MySQL 然后登录</h2>
<pre><code class="language-sql">[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# systemctl restart mysqld
[root<span class="hljs-variable">@qfedu</span>.com <span class="hljs-operator">~</span>]# mysql <span class="hljs-operator">-</span>uroot <span class="hljs-operator">-</span>p
</code></pre>
<h2 id="密码复杂度">密码复杂度</h2>
<p>安装密码插件</p>
<p>MySQL 默认启用了密码复杂度设置，插件名字叫做 validate_password</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> INSTALL PLUGIN validate_password SONAME <span class="hljs-string">&#x27;validate_password.so&#x27;</span>;
</code></pre>
<p>修改配置文件</p>
<p>修改配置</p>
<pre><code class="language-s">[root@qfedu.com ~]# vim /etc/my.cnf
[mysqld]
plugin-load=validate_password.so
validate_password_policy=0
validate-password=FORCE_PLUS_PERMANENT
</code></pre>
<p>重启MySQL生效</p>
<pre><code class="language-s">[root@qfedu.com ~]# systemctl restart mysqld
</code></pre>
<p>查看错误日志</p>
<p>登陆数据库查看</p>
<pre><code class="language-s">mysql&gt; show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.04 sec)
</code></pre>
<p><strong>validate_password_policy</strong></p>
<p>代表的密码策略，可配置的值有以下：默认是MEDIUM 0 or LOW 仅需需符合密码长度（由参数validate_password_length指定）</p>
<ol>
<li>
<p>or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符</p>
</li>
<li>
<p>or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中</p>
</li>
</ol>
<p><strong>validate_password_dictionary_file</strong></p>
<p>用于配置密码的字典文件，当validate_password_policy设置为STRONG时可以配置密码字典文 件，字典文件中存在的密码不得使用。</p>
<p><strong>validate_password_length</strong></p>
<p>用来设置密码的最小长度，默认值是8最小是0</p>
<p><strong>validate_password_mixed_case_count</strong></p>
<p>当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少同时拥有的小写和大写 字母的数量，默认是1最小是0；默认是至少拥有一个小写和一个大写字母。</p>
<p><strong>validate_password_number_count</strong></p>
<p>当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的数字的个数，默 认1最小是0</p>
<p><strong>validate_password_special_char_count</strong></p>
<p>当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的特殊字符的个 数，默认1最小是0</p>
<h2 id="密码不符合复杂性">密码不符合复杂性</h2>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> admin1@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;123&#x27;</span>;
ERROR <span class="hljs-number">1819</span> (HY000): Your password does <span class="hljs-keyword">not</span> satisfy the <span class="hljs-keyword">current</span> policy
requirements
</code></pre>
<p>处理方法：</p>
<p>查看密码策略</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">select</span> @<span class="hljs-variable">@validate</span>_password_policy; # 查看密码复杂性策略
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">select</span> @<span class="hljs-variable">@validate</span>_password_length; # 查看密码复杂性要求密码最低长度大小
</code></pre>
<p>更换符合复杂性要求的密码</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> validate_password_length<span class="hljs-operator">=</span><span class="hljs-number">1</span>; # 设置密码复杂性要求密码最低长度为<span class="hljs-number">1</span>
</code></pre>
<p>关闭复杂性策略</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> validate_password_policy<span class="hljs-operator">=</span><span class="hljs-number">0</span>; # 关闭密码复杂性策略
</code></pre>
<h1 id="mysql-登录">MySQL 登录</h1>
<pre><code class="language-sql">mysql <span class="hljs-operator">-</span>u用户名 <span class="hljs-operator">-</span>p密码 [ <span class="hljs-operator">-</span>h主机 ] [ <span class="hljs-operator">-</span>P端口 ]；
mysql <span class="hljs-operator">-</span>u用户名 <span class="hljs-operator">-</span>p密码 [ <span class="hljs-operator">-</span>h主机 ] [ <span class="hljs-operator">-</span>P端口 ] [ <span class="hljs-operator">-</span>e&quot;SQL语句&quot; ]
</code></pre>
<h1 id="mysql-权限管理">MySQL 权限管理</h1>
<h2 id="查看权限">查看权限</h2>
<pre><code class="language-sql"><span class="hljs-keyword">SHOW</span> GRANTS <span class="hljs-keyword">FOR</span> <span class="hljs-string">&#x27;用户&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span>;
</code></pre>
<p>可以不指定用户，则显示当前用户权限</p>
<p>实例</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">SHOW</span> GRANTS <span class="hljs-keyword">FOR</span> <span class="hljs-string">&#x27;root&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span>;
</code></pre>
<h2 id="授权及设置密码">授权及设置密码</h2>
<p>授权及设置密码</p>
<pre><code class="language-sql"><span class="hljs-keyword">GRANT</span> 权限 [,权限...[,权限]] <span class="hljs-keyword">NO</span> 数据库.数据表 <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;用户&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span>;
<span class="hljs-keyword">GRANT</span> 权限 [,权限...[,权限]] <span class="hljs-keyword">NO</span> 数据库.数据表 <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;用户&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;密码&#x27;</span>;
</code></pre>
<p>可以简化多次授权，并用逗号分隔</p>
<p>GRANT需要明确一下信息 要授予的权限 被授予权限的数据库或表 用户名</p>
<p>权限简介</p>
<table>
<thead>
<tr>
<th>权限</th>
<th>权限级别</th>
<th>权限说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>CREATE</td>
<td>数据库表或索引</td>
<td>创建数据库、表、索引权限</td>
</tr>
<tr>
<td>Drop</td>
<td>数据库和表</td>
<td>删除数据库和表</td>
</tr>
<tr>
<td>GRANT OPTION</td>
<td>数据库、表、保存的程序</td>
<td>赋予权限选项</td>
</tr>
<tr>
<td>REFERENCES</td>
<td>数据库或表</td>
<td></td>
</tr>
<tr>
<td>ALTER</td>
<td>表</td>
<td>更改表 比如添加字段、索引</td>
</tr>
<tr>
<td>DELETE</td>
<td>表</td>
<td>删除数据权限</td>
</tr>
<tr>
<td>INDEX</td>
<td>表</td>
<td>索引权限</td>
</tr>
<tr>
<td>INSERT</td>
<td>表</td>
<td>插入权限</td>
</tr>
<tr>
<td>SELECT</td>
<td>表</td>
<td>查询权限</td>
</tr>
<tr>
<td>UPDATE</td>
<td>表</td>
<td>更新权限</td>
</tr>
<tr>
<td>CREATE VIEW</td>
<td>视图</td>
<td>创建视图权限</td>
</tr>
<tr>
<td>SHOW VIEW</td>
<td>视图</td>
<td>查看视图权限</td>
</tr>
<tr>
<td>ALTER ROUTINE</td>
<td>存储过程</td>
<td>更改存储过程权限</td>
</tr>
<tr>
<td>CREATE ROUTINE</td>
<td>存储过程</td>
<td>创建存储过程权限</td>
</tr>
<tr>
<td>EXECUTE</td>
<td>存储过程</td>
<td>执行存储过程权限</td>
</tr>
<tr>
<td>FILE</td>
<td>服务器主机上的文件访问</td>
<td>文件访问权限</td>
</tr>
<tr>
<td>CREATE TEMPORARY TABLE</td>
<td>服务器管理</td>
<td>创建临时表权限</td>
</tr>
<tr>
<td>LOCK TABLES</td>
<td>服务器管理</td>
<td>锁表权限</td>
</tr>
<tr>
<td>CREATE USER</td>
<td>服务器管理</td>
<td>创建用户权限</td>
</tr>
<tr>
<td>RELOAD</td>
<td>服务器管理</td>
<td>执行命令权限</td>
</tr>
<tr>
<td>PROCESS</td>
<td>服务器管理</td>
<td>查看进程权限</td>
</tr>
<tr>
<td>REPLICATION CLIENT</td>
<td>服务器管理</td>
<td>复制权限</td>
</tr>
<tr>
<td>ERPLICATION SLAVE</td>
<td>服务器管理</td>
<td>复制权限</td>
</tr>
<tr>
<td>SHOW DATABASES</td>
<td>服务器管理</td>
<td>查看数据库权限</td>
</tr>
<tr>
<td>SHUTDOWN</td>
<td>服务器管理</td>
<td>关闭数据库权限</td>
</tr>
<tr>
<td>SUPER</td>
<td>服务器管理</td>
<td>执行kill线程权限</td>
</tr>
</tbody>
</table>
<p>授权设置密码实例</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> admin1@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span>;
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> admin2@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span> <span class="hljs-keyword">WITH</span> <span class="hljs-keyword">GRANT</span>
OPTION;
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> qfedu.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> admin3@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span>;
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> qfedu.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> admin3@<span class="hljs-string">&#x27;192.168.122.220&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span>
<span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span>;
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> qfedu.user <span class="hljs-keyword">TO</span> admin4@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span>;
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span>(col1),<span class="hljs-keyword">INSERT</span>(col2,col3) <span class="hljs-keyword">ON</span> qfedu.user <span class="hljs-keyword">TO</span> admin5@<span class="hljs-string">&#x27;%&#x27;</span>
IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;(Qfedu.123com)&#x27;</span>;
</code></pre>
<h2 id="mysql-8x-授权方式">MySQL 8.x 授权方式</h2>
<p>创建新的用户</p>
<pre><code class="language-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">&#x27;用户名&#x27;</span>@<span class="hljs-string">&#x27;localhost&#x27;</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">&#x27;密码&#x27;</span>;
</code></pre>
<p>数据库授权</p>
<pre><code class="language-sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> PRIVILEGES <span class="hljs-keyword">ON</span> 数据库名.<span class="hljs-operator">*</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">&#x27;用户名&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span>;
</code></pre>
<p>回收权限</p>
<pre><code class="language-sql"><span class="hljs-keyword">REVOKE</span> 权限 <span class="hljs-keyword">ON</span> 数据库.数据表 <span class="hljs-keyword">FROM</span> <span class="hljs-string">&#x27;用户&#x27;</span>@<span class="hljs-string">&#x27;IP地址&#x27;</span>;
被回收的权限必须存在，否则会出错

整个服务器，使用 <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> 和 <span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">ALL</span>；

整个数据库，使用 <span class="hljs-keyword">ON</span> datebase.<span class="hljs-operator">*</span>；

特定的表：使用 <span class="hljs-keyword">ON</span> datebase.table；
</code></pre>
<p>实例</p>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> admin1@’<span class="hljs-operator">%</span>’;
# 回收指定权限
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">ALL</span> PRIVILEGES <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> admin2@’<span class="hljs-operator">%</span>’;
# 回收所有权限
mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">ALL</span> PRIVILEGES,<span class="hljs-keyword">GRANT</span> OPTION <span class="hljs-keyword">ON</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> <span class="hljs-string">&#x27;admin2&#x27;</span>@<span class="hljs-string">&#x27;%&#x27;</span>;
# 回收多个指定权限
</code></pre>
<h2 id="刷新权限">刷新权限</h2>
<pre><code class="language-sql">mysql<span class="hljs-operator">&gt;</span> FLUSH PRIVILEGES;
flush privileges 命令本质上的作用是将当前<span class="hljs-keyword">user</span>和privilige表中的用户信息<span class="hljs-operator">/</span>权限设置从mysql库 (MySQL数据库的内置库)中提取到内存里。
</code></pre>
<p>MySQL用户数据和权限有修改后，搜索希望在&quot;不重启MySQL服务&quot;的情况下直接生效，那么就需 要执行这个命令</p>

        
        
    </body>
    </html>